<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;


class DedustingDeviceData extends Model
{
    protected $table = 'xfy_dedusting_device_data';
    protected $primaryKey = 'id';

    public static function getJsonDataOne($id, $fields, $time = 0){
        $sql = 'select ';

        foreach ($fields as $oneField) {
            if ($oneField == 'created_time') {
                $sql .= ' created_time,';
            } else {
                $sql .= "data->'$.".$oneField."' as ".$oneField.',';
            }
        }
        $sql = substr($sql,0,-1);
        $sql .= ' from xfy_dedusting_device_data ';

        $sql .= "where device_id = ?";

        if ($time) {
            $sql .= " and created_time > ? ";
        }
        $sql .= " order by created_time desc ";
        $sql .= " limit 1";
        if ($time) {
            $res = DB::select($sql, [$id, $time]);
        } else {
            $res = DB::select($sql, [$id]);
        }

        $res = json_decode( json_encode( reset($res)),true);

        return $res;
    }

    public static function getMax($id, $field, $start, $end){
        $sql = "select max(data->'$.".$field."') as max from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $max = json_decode( json_encode( reset($res)),true);
        if ($max['max']) {
            return round($max['max'],3);
        } else {
            return 0;
        }
    }

    public static function getMin($id, $field, $start, $end){
        $sql = "select min(data->'$.".$field."') as min from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $min = json_decode( json_encode( reset($res)),true);
        if ($min['min']) {
            return round($min['min'],3);
        } else {
            return 0;
        }
    }

    public static function getAvg($id, $field, $start, $end){
        $sql = "select avg(data->'$.".$field."') as avg from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $avg = json_decode( json_encode( reset($res)),true);
        if ($avg['avg']) {
            return round($avg['avg'],3);
        } else {
            return 0;
        }
    }

    public static function getJsonDataList($id, $fields, $start, $end = 0){
        $sql = 'select ';

        if (!is_array($fields)) {
            $fields = [$fields];
        }

        foreach ($fields as $oneField) {
            $sql .= "data->'$.".$oneField."' as ".$oneField.',';
        }

        if ($end) {
            $sql .= " `created_time` from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` >= ? and `created_time` <= ? order by `created_time` asc";
            $res = DB::select($sql, [$id, $start, $end]);
        } else {
            $sql .= " `created_time` from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` > ? order by `created_time` asc";
            $res = DB::select($sql, [$id, $start]);
        }
        return $res;
    }

    public static function getJsonDataListPaging($id, $fields, $start, $end = 0, $page = 0, $size = 0){
        //LIMIT ((页码-1)*页大小),页大小;
        $sql = 'select ';

        if (!is_array($fields)) {
            $fields = [$fields];
        }

        foreach ($fields as $oneField) {
            $sql .= "data->'$.".$oneField."' as ".$oneField.',';
        }

        if ($end) {
            $sql .= " `created_time` from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` >= ? and `created_time` <= ? order by `created_time` asc";
            if ($page && $size) {
                $sql .= " limit ".(($page - 1) * $size).", ".$size;
            }
            $res = DB::select($sql, [$id, $start, $end]);
        } else {
            $sql .= " `created_time` from `xfy_dedusting_device_data` where `device_id` = ? and `created_time` > ? order by `created_time` asc";
            if ($page && $size) {
                $sql .= " limit ".(($page - 1) * $size).", ".$size;
            }
            $res = DB::select($sql, [$id, $start]);
        }
        return $res;
    }

    public static function getCount($id, $start, $end){
        $sql = 'select count(*) as total';
        $sql .= ' from xfy_dedusting_device_data ';
        $sql .= "where `device_id` = ? and `created_time` >= ? and `created_time` <= ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $res = json_decode( json_encode( reset($res)),true);
        return $res['total'];
    }

    /**
     * [setDbData 缓存入库]
     */
    public static function setDbData()
    {
        $deviceTable = DedustingDevice::getModel()->getTable();

        $insertData = [];

        $devices = DedustingDevice::whereNotNull('machine_code')->pluck('id')->toArray();
        foreach ($devices as $id) {
            $cacheKey = DedustingDevice::DATA_CACHE_KEY.$id;
            $cacheData = Redis::get($cacheKey);
            if ($cacheData) {
                $data = json_decode($cacheData,true);
                if ((time() - 60) < $data['time']) {
                    $oneInsertData['device_id'] = $id;
                    $oneInsertData['data'] = $cacheData;
                    $oneInsertData['created_time'] = $data['time'];

                    array_push($insertData, $oneInsertData);
                }
            }

            //运行时间
            $totalAliveKey = DedustingDevice::TOTAL_ALIVE_CACHE_KEY.$id;
            $totalAlive = Redis::get($totalAliveKey);
            $nowAliveKey = DedustingDevice::NOW_ALIVE_CACHE_KEY.$id;
            $nowAlive = Redis::get($nowAliveKey);
            DB::table($deviceTable)
            ->where('id', $id)
            ->update([
                'total_alive' => $totalAlive ? $totalAlive : 0,
                'now_alive' => $nowAlive ? $nowAlive : 0
            ]);
        }

        //数据入库
        if ($insertData) {
            $table = self::getModel()->getTable();
            DB::table($table)->insert($insertData);
        }
    }
}